unit ProductForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.DBCtrls, Vcl.Grids,
  Vcl.DBGrids, DBModule, UserUtils, IB, Data.DB, IBDatabase, IBCustomDataSet,
  IBTable;

type
  TfmProducts = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    DBGrid1: TDBGrid;
    btnClose: TButton;
    txtProduct: TEdit;
    btnNew: TButton;
    btnDelete: TButton;
    btnSave: TButton;
    btnRefresh: TButton;
    dbcbCompany: TDBLookupComboBox;
    fbProducts: TIBTable;
    fbCustomers: TIBTable;
    dsCustomers: TDataSource;
    dsProducts: TDataSource;
    IBTransaction1: TIBTransaction;
    IBDatabase1: TIBDatabase;
    procedure btnCloseClick(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure dbcbCompanyDropDown(Sender: TObject);
  private
    { Private declarations }
    Operation: TOperation;
    procedure RefreshDB;
  public
    { Public declarations }
  end;

var
  fmProducts: TfmProducts;

implementation

{$R *.dfm}

procedure TfmProducts.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmProducts.btnDeleteClick(Sender: TObject);
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        SQL.Add('DELETE FROM CUSTOMER_PRODUCT WHERE ID=:ID');
        Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      RefreshDB;
      btnNew.Click;
    end;
  end;
end;

procedure TfmProducts.btnNewClick(Sender: TObject);
begin
  dbcbCompany.KeyValue := -1;
  txtProduct.Text := '';
  Operation := opNew;
end;

procedure TfmProducts.btnRefreshClick(Sender: TObject);
begin
  RefreshDB;
end;

procedure TfmProducts.btnSaveClick(Sender: TObject);
begin
  if (txtProduct.Text = '') or VarIsNull(dbcbCompany.KeyValue) then
  begin
    ShowMessage('Company Name and Product can be empty!');
    Exit;
  end;
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        case Operation of
          opNew:
            SQL.Add('INSERT INTO CUSTOMER_PRODUCT (CUSTOMER_ID, PRODUCT_NAME) VALUES (:CustomerID, :ProductName)');
          opEdit:
            begin
              SQL.Add('UPDATE CUSTOMER_PRODUCT SET CUSTOMER_ID=:CustomerID, PRODUCT_NAME=:ProductName WHERE ID=:ID');
              Params.ParamByName('ID').AsInteger := DBGrid1.DataSource.DataSet.FieldByName('ID').AsInteger;
            end;
        end;
        Params.ParamByName('CustomerID').AsInteger := dbcbCompany.KeyValue;
        Params.ParamByName('ProductName').AsString := txtProduct.Text;
        if not Prepared then
          Prepare;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      RefreshDB;
      btnNew.Click;
    end;
  end;
end;

procedure TfmProducts.dbcbCompanyDropDown(Sender: TObject);
begin
  with dbcbCompany.ListSource.DataSet do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmProducts.DBGrid1CellClick(Column: TColumn);
begin
  txtProduct.Text := DBGrid1.DataSource.DataSet.FieldByName('PRODUCT_NAME').AsString;
  dbcbCompany.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('CUSTOMER_ID').AsVariant;
  Operation := opEdit;
end;

procedure TfmProducts.DBGrid1TitleClick(Column: TColumn);
begin
  with fbProducts do
  begin
    IndexFieldNames := 'CUSTOMER_ID;' + Column.FieldName;
    Refresh;
  end;
end;

procedure TfmProducts.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FreeAndNil(fmProducts);
end;

procedure TfmProducts.FormCreate(Sender: TObject);
begin
  RefreshDB;
end;

procedure TfmProducts.RefreshDB;
begin
  IBDatabase1.DatabaseName := DataModule1.IBDatabase1.DatabaseName;
  IBDatabase1.Connected := False;
  IBDatabase1.Connected := True;
  IBTransaction1.Active := True;
  with fbCustomers do
  begin
    Open;
    Last;
    First;
  end;
  with fbProducts do
  begin
    Open;
    Last;
    First;
  end;
end;

end.
